package de.oszimt.matse.messenger.tests;

import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;

public class JUnitDataBase 
{
	JdbcTemplate jdbc_template;
	
	public JdbcTemplate connect(){
		BasicDataSource bds = new BasicDataSource();
	    bds.setDriverClassName("org.hsqldb.jdbcDriver");
	    bds.setUrl("jdbc:hsqldb:hsql://localhost/xdb");
	    bds.setUsername("sa");
	    bds.setPassword("oszimt");
	    JdbcTemplate temp = new JdbcTemplate(bds);
	    this.jdbc_template = temp;
	    return temp;
	}
	
	@Test
	public void tests() throws Exception
	{
		connect();

		System.out.println("Test: RegisterUser() ------------------------------------------------------------");
		getUsers();
		registerUser();
		getUsers();
		
		System.out.println("Test: GetUserData() -------------------------------------------------------------");
		getUserData();
		
		System.out.println("Test: UpdateUserData() -------------------------------------------------------------");
		getUserData();
		updateUserData();
		getUserData();

		System.out.println("Test: RemoveUser() --------------------------------------------------------------");
		getUsers();
		removeUser();
	    getUsers();

		System.out.println("Test: GetMessages() ------------------------------------------------------------");
		getMessages();

		System.out.println("Test: StoreMessage() ------------------------------------------------------------");
		getMessages();
		storeMessage();
		getMessages();
		
		System.out.println("Test: DeleteMessage() ------------------------------------------------------------");
		getMessages();
		deleteMessage();
		getMessages();

		System.out.println("Test: getContactList() ------------------------------------------------------------");
		getContactList();
		
		System.out.println("Test: AddContact() ------------------------------------------------------------");
		getContactList();
		addContact("anton", "igor");
		getContactList();
		
		System.out.println("Test: RemoveContact() ------------------------------------------------------------");
		getContactList();
		removeContact();
		getContactList();
	}

	public void getUsers() throws Exception
	{
		System.out.println(this.jdbc_template.queryForList("SELECT * FROM users"));
	}

	public void registerUser() throws Exception
	{
		this.jdbc_template.execute("INSERT INTO users (id, username, password, enabled) VALUES(5, 'test', 'test', 'true')");
	}
	
	public void getUserData() throws Exception
	{
	    System.out.println(this.jdbc_template.queryForList("SELECT * FROM users WHERE id=5"));
	}

	public void updateUserData() throws Exception
	{
		this.jdbc_template.execute("UPDATE users SET username='test', password='test', enabled='true', name='test', nachname='test', stadt='test', land='test', hobbys='test, test, test' WHERE id=5");
	}
	
	public void removeUser() throws Exception
	{
		this.jdbc_template.execute("DELETE FROM users WHERE id=5");
	}
	
	public void getMessages() throws Exception
	{
	    System.out.println(this.jdbc_template.queryForList("SELECT * FROM messages WHERE SENDER_USER_ID=1 AND RECIPIENT_USER_ID=2"));
	}
	
	public void storeMessage() throws Exception
	{
		this.jdbc_template.execute("INSERT INTO messages (id, SENDER_USER_ID, RECIPIENT_USER_ID, MESSAGE, TIME) VALUES(1, 1, 2, 'Test Nachricht', '24.01.2010 14:37')");
	}

	public void deleteMessage() throws Exception
	{
		this.jdbc_template.execute("DELETE FROM messages WHERE id=1");
	}

	public void getContactList() throws Exception
	{
	    System.out.println(this.jdbc_template.queryForList("SELECT * FROM users_users WHERE USER1_ID=1 OR USER2_ID=1"));
	}
	
	public void addContact(String userName1, String userName2) throws Exception
	{
		int user1_id = getUserId(userName1);
		int user2_id = getUserId(userName2);
		
		this.jdbc_template.execute("INSERT INTO users_users (USER1_ID, USER2_ID) VALUES("+ user1_id +", "+ user2_id +")");
	}
	
	public void removeContact() throws Exception
	{
		this.jdbc_template.execute("DELETE FROM users_users WHERE (USER1_ID=1 AND USER2_ID=2) OR (USER2_ID=1 AND USER1_ID=2)");
	}
	
	public int getUserId(String userName) throws Exception
	{
		return Integer.valueOf(this.jdbc_template.queryForList("SELECT id FROM users WHERE username='" + userName +"'").get(0).get("id").toString());
	}	
}